/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import utils.Debug;

/**
 *
 * @author daz
 */
public class TableDao {

    private DriverManagerDataSource dataSource;
    private String databaseName;
    JdbcTemplate jdbcTemplate;

    public TableDao(DriverManagerDataSource dataSource, String databaseName) {
        this.dataSource = dataSource;
        this.databaseName = databaseName;
        this.jdbcTemplate = new JdbcTemplate(dataSource, true);
    }

    public int testConnection() {
        String query = "select test from " + databaseName + ".Test";
        return jdbcTemplate.queryForInt(query);

    }

    public void deleteCp(String cpName) {
        String query = "delete from " + databaseName + ".company_info where company_name= '" + cpName + "'";
        this.jdbcTemplate.execute(query);
    }

    public List<UserInfo> getUserCp(String userName) {
        String query = "CALL " + databaseName + ".getUserCpInfo('" + userName + "')";
        return jdbcTemplate.query(query, new UserMapper());
    }

    public void marryTwoUser(String userName, String friend) {
        String query = "CALL " + databaseName + ".marryUser('" + userName + "','" + friend + "')";
        this.jdbcTemplate.execute(query);
    }

    public void deleteMessage(String userSend, String userReceive, Timestamp time) {
        String query = "CALL " + databaseName + ".deleteMessage('" + userSend + "','" + userReceive + "','" + time + "')";
        this.jdbcTemplate.execute(query);
    }

    void setReadMessage(String userSend, String userReceive, Timestamp timeSend) {
        String query = "CALL " + databaseName + ".setMessageRead"
                + "('" + userSend + "','" + userReceive + "','" + timeSend + "')";
        this.jdbcTemplate.execute(query);
    }

    void setAvatar(int val, String name) {
        String query = "CALL " + databaseName + ".setAvatar"
                + "(" + val + ",'" + name + "')";
        this.jdbcTemplate.execute(query);
    }

    public int isCpCreated(String companyName) {
        String query = "CALL " + databaseName + ".isCpCreated('" + companyName + "')";
        return this.jdbcTemplate.queryForInt(query);
    }

    List<SmsInfo> getSmsQueue() {
        String query = "CALL getSmsNap()";
        return jdbcTemplate.query(query, new SmsQueueMapper());
    }

    void logTransaction() {
        String query = "CALL logTransaction()";
        jdbcTemplate.execute(query);
    }

    List<UserInfo> getListUser(int number) {
        String query = "SELECT * FROM " + databaseName + ".user_manager u where id>=1207 and id <=2207 limit " + number;
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    List<UserInfo> getRobotUser(int number) {
        String query = "SELECT * FROM poker_online.user_manager "
                + " where is_online=0  "
                + " ORDER BY id  limit " + number;
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    public int addNewCp(String companyName, String numberActivate, String cpSyntax, String numberCharge1,
            String numberCharge2, String numberCharge3, String cost1, String cost2, String cost3, String coefficient) {
        String query = "CALL poker_online.addNewCp('" + companyName + "','" + numberActivate + "','" + cpSyntax + "','" + numberCharge1 + "',"
                + "'" + numberCharge2 + "','" + numberCharge3 + "'," + cost1 + "," + cost2 + "," + cost3 + "," + coefficient + ")";
        return this.jdbcTemplate.queryForInt(query);
    }

    public void addManyVendor(String companyName, String numberActivate, String cpSyntax, String numberCharge1,
            String numberCharge2, String numberCharge3, String cost1, String cost2, String cost3, String coefficient, int numberVendor) {
        String query = "CALL poker_online.addManyVendor('" + companyName + "','" + numberActivate + "','" + cpSyntax + "','" + numberCharge1 + "',"
                + "'" + numberCharge2 + "','" + numberCharge3 + "'," + cost1 + "," + cost2 + "," + cost3 + "," + coefficient + "," + numberVendor + ")";
        this.jdbcTemplate.execute(query);
    }

    public void editCp(String companyName, String numberActivate, String cpSyntax, String numberCharge1,
            String numberCharge2, String numberCharge3, String cost1, String cost2, String cost3, String coefficient) {
        String query = "CALL poker_online.editCpInfo('" + companyName + "','" + numberActivate + "','" + cpSyntax + "','" + numberCharge1 + "',"
                + "'" + numberCharge2 + "','" + numberCharge3 + "'," + cost1 + "," + cost2 + "," + cost3 + "," + coefficient + ")";
        this.jdbcTemplate.execute(query);
    }

    int getNumberOnline() {
        return this.jdbcTemplate.queryForInt("select count(*) "
                + " from poker_online.user_manager "
                + " where is_online= 1;");
    }

    int countSms(String number, String date, String moth, String year) {
        String query = " "
                + "SELECT count(*) FROM poker_online.SMS_RECEIVE_LOG s where "
                + " gen_date like '" + year + "-" + moth + "-" + date + "%' "
                + " and Service_Number='" + number + "'";
        Debug.d("query :" + query);
        return this.jdbcTemplate.queryForInt(query);
    }

    int countCard(int cardValue, String date, String moth, String year) {
        return this.jdbcTemplate.queryForInt(" "
                + " SELECT count(*) FROM poker_online.user_card_log u "
                + "where gen_date like '" + year + "-" + moth + "-" + date + "%' and amount=" + cardValue);
    }

    void updateAdString(String text) {
        this.jdbcTemplate.execute("update poker_online.value_info v set ad_string='" + text + "';");
    }

    private static final class SmsQueueMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            SmsInfo smsInfo = new SmsInfo();
            smsInfo.setInfo(rs.getString("Info"));
            smsInfo.setServiceCode(rs.getString("Service_Number"));
            return smsInfo;
        }
    }

    private static final class UserMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserInfo userInfo = new UserInfo();
            userInfo.setUserName(rs.getString("user_name"));
            userInfo.setPhoneNumber(rs.getString("phone_number"));
            userInfo.setCompanyId(rs.getInt("company_id"));
            userInfo.setCompanyName(rs.getString("company_name"));
            return userInfo;
        }
    }

    public List<UserInfo> getUserRegisterInfo(String userName) {
        String query = "call " + databaseName + ".findUserName('" + userName + "')";
        return jdbcTemplate.query(query, new UserMapper());
    }

    public void setUserOnline(int state, String userName) {
        String query = "CALL " + databaseName + ".setUserOnline(" + state + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void changeLoginId(long id, String userName) {
        String query = "Call " + databaseName + ".changeServerId(" + id + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void resetUserIsplay() {
        String query = "call " + databaseName + ".resetUserIsOnline()";
        this.jdbcTemplate.execute(query);
    }

    public void insertUserRegister(String userName, String password, String gender, String companyId) {
        String query = "CALL " + databaseName + ".insertUserRegister('" + userName + "','" + password + "'," + gender + "," + companyId + ")";
        // Debug.d("query:" + query);
        this.jdbcTemplate.execute(query);
    }

    public void updateMoney(int moneyChange, String userName) {
        String query = "CALL " + databaseName + ".updateMoney(" + moneyChange + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void updateLevel(String userName, int newLevel) {
        String query = "CALL " + databaseName + ".updateLevel('" + userName + "'," + newLevel + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void updateWin(String userName, int newWin) {
        String query = "CALL " + databaseName + ".updateWin('" + userName + "'," + newWin + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void updateLose(String userName, int newLose) {
        String query = "CALL " + databaseName + ".updateLose('" + userName + "'," + newLose + ") ";
        this.jdbcTemplate.execute(query);
    }

    public void insertNewFriend(String userName1, String userName2) {
        String query = "CALL " + databaseName + ".insertNewFriend('" + userName1 + "','" + userName2 + "')";
        this.jdbcTemplate.execute(query);

    }

    public void marry(String userName1, String userName2) {
        String query = "CALL " + databaseName + ".changeMariedState('" + userName1 + "','" + userName2 + "')";
        this.jdbcTemplate.execute(query);
    }

    public void insertNewMessenger(String userSend, String userReceive, String message, Timestamp timeSend, int kind) {
        String query = "CALL " + databaseName + ".insertNewMessage('" + userSend + "','" + userReceive + "','" + message + "','" + timeSend + "'," + kind + ")";
        this.jdbcTemplate.execute(query);
    }

    public List<UserInfo> getTopLevel() {
        String query = "CALL " + databaseName + ".getTopLevel()";
        return this.jdbcTemplate.query(query, new UserMapper());
    }

    public List<UserInfo> getTopRich() {
        String query = "CALL " + databaseName + ".getTopRich()";
        return this.jdbcTemplate.query(query, new UserMapper());
    }
//
//    private static final class UserInfoMapper implements RowMapper {
//
//        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
//            UserInfo userInfo = new UserInfo();
//            userInfo.setUserName(rs.getString("user_name"));
//            userInfo.setGender(rs.getInt("gender"));
//            userInfo.setMoney(rs.getInt("money"));
//            userInfo.setLevel(rs.getInt("level"));
//            userInfo.setAvatarId(rs.getInt("has_avatar"));
//            return userInfo;
//        }
//    }

    public void insertUserStatus(String userName, String status) {
        String query = "CALL " + databaseName + ".insertUserStatus('" + userName + "','" + status + "')";
        this.jdbcTemplate.execute(query);


    }

    public void insertNewBuyItem(int isActivated, int kind, String userName) {
        String query = "Call " + databaseName + ".insertNewBuyItem(" + isActivated + "," + kind + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public void insertNewItemHistory(int kindItem, int kindAct, String userName) {
        String query = "Call " + databaseName + ".insertNewItemHistory(" + kindItem + "," + kindAct + ",'" + userName + "')";
        this.jdbcTemplate.execute(query);
    }

    public List<ShopHistoryItem> getUserShopHistory(String userName) {
        String query = "CALL " + databaseName + ".getUserShopHistory('" + userName + "')";
        return this.jdbcTemplate.query(query, new ShopHistoryListMapper());
    }

    private static final class ShopHistoryListMapper implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int i) throws SQLException {
            int kindItem = rs.getInt("kind_item");
            int kindAct = rs.getInt("kind_act");
            Timestamp time = rs.getTimestamp("time_act");
            ShopHistoryItem item = new ShopHistoryItem(kindItem, kindAct, time.toString());
            return item;
        }
    }

    public void activateItem(Timestamp newTime, Timestamp oldTime, String userName, int kind) {
        String query = "CALL " + databaseName + ".activateItem('" + newTime + "','" + oldTime + "','" + userName + "'," + kind + ")";
        this.jdbcTemplate.execute(query);
    }

    public void deleteItemUsed(String name, Timestamp timeUse) {
        String query = "CALL " + databaseName + ".deleteUserItem('" + name + "','" + timeUse + "')";
        this.jdbcTemplate.execute(query);
    }

    public void inserChild(int familyId, String childName, int childGender) {
        String query = "CALL " + databaseName + ".insertFamilyInfo(" + familyId + ",'" + childName + "'," + childGender + ");";
        this.jdbcTemplate.execute(query);
    }

    void changePass(String userName, String newPass) {
        String query = "CALL changePass('" + userName + "','" + newPass + "')";
        jdbcTemplate.execute(query);
    }

    void activateAcc(String name) {
        String query = "CALL " + databaseName + ".activateAcc('" + name + "')";
        jdbcTemplate.execute(query);
    }

    public List<CompanyInfo> getSMSInfo(String companyName) {
        String query = "select * from " + databaseName + ".company_info where company_name ='" + companyName + "'";
        return this.jdbcTemplate.query(query, new SmsInfoMapper());
    }

    public List<CompanyInfo> getSMSInfo() {
        String query = "select * from " + databaseName + ".company_info ";
        return this.jdbcTemplate.query(query, new SmsInfoMapper());
    }

    private static final class SmsInfoMapper implements RowMapper {

        @Override
        public CompanyInfo mapRow(ResultSet rs, int i) throws SQLException {
            CompanyInfo companyInfo = new CompanyInfo();
            companyInfo.setSynTax(rs.getString("syntax"));
            companyInfo.setNumberActive(rs.getString("number_activate"));
            companyInfo.setNumberCharge1(rs.getString("number_charge1"));
            companyInfo.setCost1(rs.getInt("cost1"));
            companyInfo.setNumberCharge2(rs.getString("number_charge2"));
            companyInfo.setCost2(rs.getInt("cost2"));
            companyInfo.setNumberCharge3(rs.getString("number_charge3"));
            companyInfo.setCost3(rs.getInt("cost3"));
            companyInfo.setCoefficient(rs.getInt("coefficient"));
            companyInfo.setCompanyId(rs.getInt("id"));
            companyInfo.setName(rs.getString("company_name"));
            return companyInfo;
        }
    }

    public List<CompanyInfo> getVendorInfo() {
        String query = "SELECT *,min(vendor_id) as min FROM poker_online.vendor_info v group by company_name";
        return this.jdbcTemplate.query(query, new VendorInfoMapper());
    }

    private static final class VendorInfoMapper implements RowMapper {

        @Override
        public CompanyInfo mapRow(ResultSet rs, int i) throws SQLException {
            CompanyInfo companyInfo = new CompanyInfo();
            companyInfo.setSynTax(rs.getString("syntax"));
            companyInfo.setNumberActive(rs.getString("number_activate"));
            companyInfo.setNumberCharge1(rs.getString("number_charge1"));
            companyInfo.setCost1(rs.getInt("cost1"));
            companyInfo.setNumberCharge2(rs.getString("number_charge2"));
            companyInfo.setCost2(rs.getInt("cost2"));
            companyInfo.setNumberCharge3(rs.getString("number_charge3"));
            companyInfo.setCost3(rs.getInt("cost3"));
            companyInfo.setCoefficient(rs.getInt("coefficient"));
            companyInfo.setCompanyId(rs.getInt("min"));
            companyInfo.setName(rs.getString("company_name"));
            return companyInfo;
        }
    }
}
